package cn.dida.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import cn.dida.model.Pageable;
import cn.dida.model.Staff;
import cn.dida.model.Sysres;
import cn.dida.util.MD5Util;

/**
 * 账户管理 数据交互类
 * @author weiwei
 */
@Repository
public class StaffDao extends DaoBase {

	/**
	 * 根据用户名和密码获取用户(用于登录)
	 * @param account 账户名
	 * @param pwd 密码
	 * @return Staff 账户
	 * */
    public Staff get(String account, String pwd) {
        String sql = "select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id where stf.account = ? and stf.pwd = ?";
        List<Staff> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Staff.class),account,pwd);
        if(result.size() <= 0){//适用于已加密的密码
        	result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Staff.class),account,MD5Util.getMD5String(pwd));
        }
        if(result.size() > 0){
        	return result.get(0);
        }else{
        	return null;
        }
        
    }
    
    /**
     * 根据账户名称获取账户信息
     * @param account 账户名称
     * @param companyid 
     * @return Staff 账户
     */
    public Staff getByAccount(String account, Integer companyid) {
        String sql = "select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id where stf.account = ? and stf.company_id = ?";
        try {
            Staff Staff = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Staff>(Staff.class), account,companyid);
            return Staff;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据电话获取账户信息
     * @param mobile 联系电话
     * @param companyid 
     * @return Staff 账户
     */
    public Staff getByMobile(String mobile, Integer companyid) {
        String sql = "select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id where stf.mobile = ? and stf.company_id = ?";
        try {
            Staff Staff = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Staff>(Staff.class), mobile,companyid);
            return Staff;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
	 * 根据用户名获取用户
	 * @param account 账户名
	 * @return Staff 账户
	 * */
    public Staff get(String account) {
        String sql = "select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id where stf.account = ?";
        try {
            Staff Staff = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Staff>(Staff.class), account);
            return Staff;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
   	 * 根据公司id和账户id获取用户
   	 * @param companyId 公司id
   	 * @param staffId 账户id
   	 * @return Staff 账户
   	 * */
    public Staff get(Integer companyId, Integer staffId) {
        String sql = "select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id where stf.id = ? and stf.company_id = ?";
        try {
            Staff Staff = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Staff>(Staff.class), staffId, companyId);
            return Staff;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 账户管理列表
     * @param companyId 公司id
     * @param keyword 查询条件
     * @param state 账户状态,1正常，2停用
     * @param page 分页游标
     * @return Pageable<Staff> 账户管理分页列表
     * */
    public Pageable<Staff> query(Integer companyId, String keyword, Integer state, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from staff stf join company cpn on stf.company_id = cpn.id");
        StringBuffer sqlQueryResult = new StringBuffer("select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id");

        StringBuffer sqlWhere = new StringBuffer(" where stf.company_id = ?");
        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and stf.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (keyword != null) {
            sqlWhere.append(" and stf.account like ?");
            args = ArrayUtils.add(args, "%"+keyword+"%");
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by stf.id asc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Staff> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Staff.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }
    /**
     * 账户管理列表（数据导出用）
     * @param companyId 所属公司id
     * @param keyword 查询条件
     * @param state 账户状态：1正常，2停用
     * @return List<Staff> 账户列表
     * @date 2016-12-08 14:16
     * */
    public List<Staff> queryForExport(Integer companyId, String keyword, Integer state) {
    	Object[] args = {};
    	StringBuffer sqlQueryResult = new StringBuffer("select stf.*,cpn.name 'company_name' from staff stf join company cpn on stf.company_id = cpn.id");
    	
    	StringBuffer sqlWhere = new StringBuffer(" where stf.company_id = ?");
    	args = ArrayUtils.add(args, companyId);
    	if (state != null) {
    		sqlWhere.append(" and stf.state = ?");
    		args = ArrayUtils.add(args, state);
    	}
    	if (StringUtils.isNotBlank(keyword)) {
    		sqlWhere.append(" and stf.account like ?");
            args = ArrayUtils.add(args, "%"+keyword+"%");
    	}
    	
    	// 查询记录
    	sqlQueryResult.append(sqlWhere).append("  order by stf.create_time desc ");
    	List<Staff> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Staff.class));
    	return result;
    }

    /**
     * 根据公司id和账户id获取系统资源
     * @param companyId 公司id
   	 * @param staffId 账户id
   	 * @return List<Sysres> 系统资源集合
     * */
    public List<Sysres> getStaffRes(Integer companyId, Integer staffId) {
        String sql = "select srs.* from sysres srs join staff_sysres stf_srs on srs.id = stf_srs.sysres_id join staff stf on stf.id = stf_srs.staff_id where stf.company_id = ? and stf.id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Sysres.class), companyId, staffId);
    }

    /**
     * 新增账户
     * @param staff 账户实体信息
     * @return int 受影响的记录行数
     * */
    public int insert(Staff staff) {
        String sql = "insert into staff(company_id, account, pwd, mobile, admin, state, create_time) values (:company_id, :account, :pwd, :mobile, :admin, :state, :create_time)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("company_id", staff.getCompanyId());
        paramMap.put("account", staff.getAccount());
        paramMap.put("pwd", MD5Util.getMD5String(staff.getPwd()));
        paramMap.put("mobile", staff.getMobile());
        paramMap.put("admin", staff.getAdmin());
        paramMap.put("state", staff.getState());
        paramMap.put("create_time", new Date());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 更新账户
     * @param staff 账户实体信息
     * @return int 受影响的记录行数
     * */
    public int update(Staff staff) {
        String sql = "update staff set account=:account, mobile=:mobile, pwd=:pwd, state=:state where company_id = :company_id and id = :id";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("id", staff.getId());
        paramMap.put("company_id", staff.getCompanyId());
        paramMap.put("account", staff.getAccount());
        paramMap.put("mobile", staff.getMobile());
        paramMap.put("pwd", staff.getPwd());
        paramMap.put("state", staff.getState());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 更新账户权限
     * @param companyId 公司id
     * @param staffId 账户id
     * @param authIds 权限id
     * @return int[] 数组的每个元素值对应每条SQL影响的数据库的记录数
     * */
    public int[] updateAuth(Integer companyId, Integer staffId, Integer... authIds) {
        String resetSql = "delete srs from staff_sysres srs join staff stf on srs.staff_id = stf.id where stf.company_id = ? and stf.id = ?";
        jdbcTemplate.update(resetSql, companyId, staffId);

        String insertSql = "insert into staff_sysres(staff_id, sysres_id) values (?, ?)";
        List<Object[]> batchParam = new ArrayList<>();
        for (Integer authId : authIds) {
            batchParam.add(new Object[]{staffId, authId});
        }
        return jdbcTemplate.batchUpdate(insertSql, batchParam);
    }


    /**
     * 删除账户
     * @param companyId 公司id
     * @param id 账户id
     * @return int 受影响的记录行数
     * */
    public int delete(Integer companyId, Integer id) {
        String sql = "delete from staff where company_id = ? and id = ?";
        return jdbcTemplate.update(sql, companyId, id);
    }
    /**
     * 账户密码重置
     * @param companyId 公司id（被重置用户的）
     * @param id 账户id 被重置用户的）
     * @return int 受影响的记录行数
     */
    public int resetPassword(Integer companyId, Integer id){
    	String pwd = MD5Util.getMD5String("qrcode123456");
    	String sql = "update staff set pwd = ? where company_id = ? and id = ?";
    	int i = jdbcTemplate.update(sql, pwd,companyId,id);
    	return i;
    }
    /**
     * 校验原密码（修改密码时使用）
     * @param companyId 公司id
     * @param id 帐户id
     * @param oldPassword 原密码
     * @return int 0输入的原密码不正确，1输入的原密码正确
     */
    public int checkOldPassword(Integer companyId,Integer id,String oldPassword){
    	String sql = "select * from staff where company_id = ? and id = ?";
    	List<Staff> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Staff.class),companyId,id);
    	int flag = 0;
    	if(result.size() > 0){
    		Staff staff = result.get(0);
    		if(staff.getPwd().equals(oldPassword)){//未加密的密码
    			flag = 1;
    		}else if(staff.getPwd().equals(MD5Util.getMD5String(oldPassword))){
    			flag = 1;
    		}
    	}
    	return flag;
    }
    /**
     * 修改密码
     * @param companyId 当前登录用户 公司id
     * @param id 当前登录用户 账户id
     * @param password 新密码
     * @return int 受影响的记录行数
     */
    public int editPassword(Integer companyId,Integer id, String newPassword){
    	String pwd = MD5Util.getMD5String(newPassword);
    	String sql = "update staff set pwd = ? where company_id = ? and id = ?";
    	int i = jdbcTemplate.update(sql, pwd,companyId,id);
    	return i;
    }
}
